package com.yuda.afterexamscore.controller;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ZipUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import com.yuda.afterexamscore.domain.*;
import com.yuda.afterexamscore.service.ISpartaSubjectService;
import com.yuda.common.core.utils.file.FileUtils;
import com.yuda.common.core.web.domain.AjaxResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * 导表Controller
 *
 * @author fmx
 * @date 2021-05-21
 */
@RestController
@RequestMapping("/exportScoreExcel")
@Api("导excel表Controller")
@RefreshScope
public class ExportScoreExcelController {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Value("${examUUID}")
    private String examUUID;

    @Autowired
    private ISpartaSubjectService spartaSubjectService;

    @Autowired
    private RedisTemplate redisTemplate;

    private static Float process = 0f;

    /**
     * 根据 subjectRelationId 获取该科目小题分
     *
     */
    @ApiOperation("生成excel表，传excelType类型，1校考，2标考")
    @PostMapping("/{excelType}")
    public void exportScoreExcel(@PathVariable("excelType")String excelType) throws Exception {
        process = 0f;
        Boolean remove = redisTemplate.hasKey(examUUID + "RemoveSubject");
        //查询已存在的单科
        List<SpartaSubject> subjects = jdbcTemplate.query("SELECT a.subject_relation_id relationSubjectId,CONCAT(b.desc,':',b.name) name\n" +
                "from dp_subject_score a \n" +
                "LEFT JOIN sparta_subject b on a.subject_relation_id = b.relation_subject_id and b.relation_subject_id not like '%#%'\n" +
                "GROUP BY a.subject_relation_id", new BeanPropertyRowMapper<>(SpartaSubject.class));
        if (remove){
            //有,则排除插入
            Object removeSubject = redisTemplate.opsForValue().get(examUUID + "RemoveSubject");
            String removeSubjectStr = removeSubject.toString();
            subjects = subjects.stream().filter(s -> !removeSubjectStr.contains(s.getRelationSubjectId())).collect(Collectors.toList());
        }
        //查询有多少学科,分摊查询进度为学科表分80%的进度
        int processSize = subjects.size();
        Map<Object, String> stringObjectMap = subjects.stream().collect(Collectors.toMap(SpartaSubject::getRelationSubjectId, SpartaSubject::getName));
        Set<Object> strings = stringObjectMap.keySet();
        process = 5f;
        for (Object subjectRelationId : strings) {
            FileUtils.deleteFile("/data" + File.separator + examUUID + "Excel/" + stringObjectMap.get(subjectRelationId) + "小题分.xlsx");
            FileUtils.deleteFile("/data" + File.separator + examUUID + "Excel/" + stringObjectMap.get(subjectRelationId) + "小题答案.xlsx");
//            BigExcelWriter writer = ExcelUtil.getBigWriter("D:" + File.separator + examUUID + "Excel/" + stringObjectMap.get(subjectRelationId) + "小题分.xlsx");
//            BigExcelWriter writeranswer = ExcelUtil.getBigWriter("D:" + File.separator + examUUID + "Excel/" + stringObjectMap.get(subjectRelationId) + "小题答案.xlsx");
            BigExcelWriter writer = ExcelUtil.getBigWriter("/data" + File.separator + examUUID + "Excel/" + stringObjectMap.get(subjectRelationId) + "小题分.xlsx");
            BigExcelWriter writeranswer = ExcelUtil.getBigWriter("/data" + File.separator + examUUID + "Excel/" + stringObjectMap.get(subjectRelationId) + "小题答案.xlsx");
            String sql = "SELECT " +
                    " '---' as name ,'---' as 'bmh','考试满分' as 'kskm','---' as 'zkzNum',";
            String sql2 = "SELECT " +
                    " '---' as name ,'---' as 'bmh','正确答案' as 'kskm','---' as 'zkzNum',";
            String queryQuesIndexSql = "SELECT ques_index,question_type from dp_ques_index_mapping where subject_relation_id = '" + subjectRelationId + "' ORDER BY ques_index+0";
            List<DpQuesIndexMapping>  quesIndexList = jdbcTemplate.query(queryQuesIndexSql, new BeanPropertyRowMapper<>(DpQuesIndexMapping.class));
            StringBuilder scoreBuilder = new StringBuilder();
            StringBuilder scoreBuilder2 = new StringBuilder();
            StringBuilder answerBuilder = new StringBuilder();
            StringBuilder answerBuilder2 = new StringBuilder();
            scoreBuilder.append("select e.name, e.sfz_num '身份证','" + stringObjectMap.get(subjectRelationId) + "' '考试科目',t.* FROM ( SELECT " +
                    "a.zkz_num,");
            scoreBuilder2.append(sql);
            answerBuilder.append("select e.name, e.sfz_num '身份证','" + stringObjectMap.get(subjectRelationId) + "' '考试科目',t.* FROM ( SELECT " +
                    "a.zkz_num,");
            answerBuilder2.append(sql2);

            int length = quesIndexList.size();
            //创建表头
            writer.addHeaderAlias("name", "姓名");
            writer.addHeaderAlias("bmh", "身份证");
            writer.addHeaderAlias("kskm", "考试科目");
            writer.addHeaderAlias("zkzNum", "准考证");

            writeranswer.addHeaderAlias("name", "姓名");
            writeranswer.addHeaderAlias("bmh", "身份证");
            writeranswer.addHeaderAlias("kskm", "考试科目");
            writeranswer.addHeaderAlias("zkzNum", "准考证");

            for (int i = 0; i < length; i++) {
                writer.addHeaderAlias("t" + (i + 1), "第" + (i + 1) + "题");
                writeranswer.addHeaderAlias("t" + (i + 1), "第" + (i + 1) + "题");
                if (i != length - 1) {
                    scoreBuilder2.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN m.full_score+'' ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex() + ",");
                    scoreBuilder.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN a.score ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex() + ",");
                    if (quesIndexList.get(i).getQuestionType()==6){
                        answerBuilder2.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN m.right_answer ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex() + ",");
                        answerBuilder.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN a.answer ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex() + ",");
                    }else {
                        answerBuilder2.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.right_answer,'#',''),1,'A'),2,'B') ,3,'C'),4,'D'),5,'E'),6,'F'),7,'G'),'-A',''),'-B',''),8,'H') ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex() + ",");
                        answerBuilder.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.answer,'#',''),1,'A'),2,'B') ,3,'C'),4,'D'),5,'E'),6,'F'),7,'G'),'-A',''),'-B',''),8,'H') ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex() + ",");
                    }

                } else {
                    scoreBuilder2.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN m.full_score+'' ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex());
                    scoreBuilder.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN a.score ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex());
                    if (quesIndexList.get(i).getQuestionType()==6 || quesIndexList.get(i).getQuestionType()==8){
                        answerBuilder2.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN m.right_answer ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex());
                        answerBuilder.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN a.answer ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex());
                    }else {
                        answerBuilder2.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.right_answer,'#',''),1,'A'),2,'B') ,3,'C'),4,'D'),5,'E'),6,'F'),7,'G'),'-A',''),'-B',''),8,'H') ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex());
                        answerBuilder.append("MAX(CASE ques_index WHEN '" + quesIndexList.get(i).getQuesIndex() + "' THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.answer,'#',''),1,'A'),2,'B') ,3,'C'),4,'D'),5,'E'),6,'F'),7,'G'),'-A',''),'-B',''),8,'H') ELSE '' END ) t" + quesIndexList.get(i).getQuesIndex());
                    }
                }
            }

            scoreBuilder2.append(" from dp_ques_index_mapping m where m.subject_relation_id = '" + subjectRelationId + "' union ");
            answerBuilder2.append(" from dp_ques_index_mapping m where m.subject_relation_id = '" + subjectRelationId + "' union ");
            scoreBuilder.append(" FROM dp_ques_score a " +
                    " where a.subject_relation_id = '" + subjectRelationId + "' " +
                    " GROUP BY zkz_num " +
                    ") t " +
                    "LEFT JOIN sparta_exam_student e on e.zkz_num = t.zkz_num and e.e_status = 0 and e.student_id !='000000' " +
                    "order by zkzNum+0");
            answerBuilder.append(" FROM dp_ques_score a " +
                    " where a.subject_relation_id = '" + subjectRelationId + "' " +
                    " GROUP BY zkz_num " +
                    ") t " +
                    "LEFT JOIN sparta_exam_student e on e.zkz_num = t.zkz_num and e.e_status = 0 and e.student_id !='000000' " +
                    "order by zkzNum+0");

            String execScoreSql = scoreBuilder2.append(scoreBuilder).toString();
            System.out.println(execScoreSql);

            String execAnswerSql = answerBuilder2.append(answerBuilder).toString();
            System.out.println(execAnswerSql);

            BeanPropertyRowMapper<Excel610> beanPropertyRowMapper2 = new BeanPropertyRowMapper<>(Excel610.class);
            List<Excel610> excel610s = jdbcTemplate.query(execScoreSql, beanPropertyRowMapper2);
            List<Excel610> excel610a = jdbcTemplate.query(execAnswerSql, beanPropertyRowMapper2);
            System.out.println("生成excel中........" + stringObjectMap.get(subjectRelationId));
            writer.setOnlyAlias(true);
            writeranswer.setOnlyAlias(true);
            writer.write(excel610s, true);
            writeranswer.write(excel610a, true);
            writer.close();
            writeranswer.close();
            process = process + 80/processSize;
        }
        process = 85f;
        //查询总分是否可以算
        int zongfenType = 1;
        List<SpartaSubject> spartaSubjects = spartaSubjectService.selectSpartaSubjectList(null);
        //排除删除科目
        if (remove){
            //有,则排除插入
            Object removeSubject = redisTemplate.opsForValue().get(examUUID + "RemoveSubject");
            String removeSubjectStr = removeSubject.toString();
            spartaSubjects = spartaSubjects.stream().filter(s -> !removeSubjectStr.contains(s.getRelationSubjectId())).collect(Collectors.toList());
        }
        //查询examuuid
        String examuuid = jdbcTemplate.queryForObject("select uuid from sparta_exam", String.class);
        for (SpartaSubject spartaSubject : spartaSubjects) {
            Object formalAnswer = redisTemplate.opsForHash().get(examUUID + "ProcessOf" + spartaSubject.getRelationSubjectId(), "formalAnswer");
            Object quesScore = redisTemplate.opsForHash().get(examUUID + "ProcessOf" + spartaSubject.getRelationSubjectId(), "quesScore");
            Object subjectScore = redisTemplate.opsForHash().get(examUUID + "ProcessOf" + spartaSubject.getRelationSubjectId(), "subjectScore");
            if (formalAnswer.toString().equals("0")||quesScore.toString().equals("0")||subjectScore.toString().equals("0")){
                zongfenType = 0;
            }
        }
        if (zongfenType == 1 && excelType.equals("1")){    //算总分 校考版本
            exportXiaoZhaoType();
        }else if (zongfenType == 1 && excelType.equals("2")){
            exportBiaoKaoType();                          //算总分 标考版本
        }
        System.out.println("生成完毕,正在压缩");
        ZipUtil.zip("/data" + File.separator + examUUID + "Excel");
        process = 100f;
    }

    @ApiOperation("判断是否存在压缩包")
    @GetMapping("/getExcelZipExsit")
    public AjaxResult getExcelZipExsit() throws IOException {
        boolean exist = FileUtil.exist("/data" + File.separator + examUUID + "Excel.zip");
        return AjaxResult.success(exist);
    }

    @ApiOperation("返回压缩包")
    @GetMapping("/getExcelZip")
    public void getExcelZip(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //下载
        System.out.println("压缩完毕,正在下载");
        String filename = examUUID + "Excel.zip";
        //File.separator代表了当前系统的文件分割符
        //linux使用/分割符会报错找不到文件,window系统下/和\都可以作为文件路径
//        File zipFile = new File("D:" + File.separator + examUUID + "Excel.zip");
        File zipFile = new File("/data" + File.separator + examUUID + "Excel.zip");
        FileInputStream in = new FileInputStream(zipFile);
        String agent = request.getHeader("user-agent");
        if (agent.contains("FireFox")) {
            filename = new String(filename.getBytes("UTF-8"), "iso-8859-1");
        } else {
            filename = URLEncoder.encode(filename, "UTF-8");
        }
        // 设置下载文件的mineType，告诉浏览器下载文件类型
        String mineType = request.getServletContext().getMimeType(filename);
        response.setContentType(mineType);
        // 设置一个响应头，无论是否被浏览器解析，都下载
        response.setHeader("Content-disposition", "attachment; filename=" + filename);
        // 将要下载的文件内容通过输出流写到浏览器
        OutputStream out = response.getOutputStream();
        int len = 0;
        byte[] buffer = new byte[1024];
        while ((len = in.read(buffer)) > 0) {
            out.write(buffer, 0, len);
        }
        out.close();
        in.close();
        process = 0f;
    }

    @ApiOperation("获取导表进度")
    @GetMapping("/queryexportScoreExcelProcess")
    public AjaxResult queryinsertFromDpSubjectScoreProcess() {
        return AjaxResult.success(ExportScoreExcelController.process);
    }

    public void exportBiaoKaoType(){
        String examuuid = jdbcTemplate.queryForObject("select uuid from sparta_exam", String.class);
        String sql = " select relation_subject_id id,name from sparta_subject where relation_subject_id not like '%#%'";
        List<String[]> list = jdbcTemplate.query(sql, (rs, r) -> {
            return new String[]{rs.getString("id"), rs.getString("name")};
        });

        StringBuilder sb = new StringBuilder();
        sb.append("SELECT DISTINCT d.province_name provinceName," +
                "d. NAME schoolNmae,f.name ,b.score ,b.percentage_g percentageG,b.percentage_s percentageS," +
                "t.* from ( SELECT a.zkz_num zkzNum,a.sfz_num sfzNum,");
        int size = list.size();
//            BigExcelWriter writerTotal = ExcelUtil.getBigWriter("D:" + File.separator + examUUID + "Excel/总分.xlsx");
        FileUtils.deleteFile("/data" + File.separator + examUUID + "Excel/总分.xlsx");
        BigExcelWriter writerTotal = ExcelUtil.getBigWriter("/data" + File.separator + examUUID + "Excel/总分.xlsx");
        writerTotal.addHeaderAlias("provinceName","省份");
        writerTotal.addHeaderAlias("schoolNmae","学校");
        writerTotal.addHeaderAlias("name","姓名");
        writerTotal.addHeaderAlias("score","总分");
        writerTotal.addHeaderAlias("percentageG","总分全国百分比排名");
        writerTotal.addHeaderAlias("percentageS","总分全省百分比排名");
        for (int i = 0; i < size ; i++) {
            String[] mapping = list.get(i);
            if(i != size-1) {
                writerTotal.addHeaderAlias("subjectScore"+(i+1),mapping[1]);
                writerTotal.addHeaderAlias("subjectRankG"+(i+1),mapping[1]+"全国百分比排名");
                writerTotal.addHeaderAlias("subjectRankS"+(i+1),mapping[1]+"全省百分比排名");
                sb.append(" MAX(CASE subject_relation_id WHEN '"+mapping[0]+"' THEN a.score ELSE '' END ) subjectScore"+(i+1)+", ");
                sb.append(" MAX(CASE subject_relation_id WHEN '"+mapping[0]+"' THEN  a.percentage_g ELSE '' END ) subjectRankG"+(i+1)+", ");
                sb.append(" MAX(CASE subject_relation_id WHEN '"+mapping[0]+"' THEN  a.percentage_s  ELSE '' END ) subjectRankS"+(i+1)+", ");
            } else {
                writerTotal.addHeaderAlias("subjectScore"+(i+1),mapping[1]);
                writerTotal.addHeaderAlias("subjectRankG"+(i+1),mapping[1]+"全国百分比排名");
                writerTotal.addHeaderAlias("subjectRankS"+(i+1),mapping[1]+"全省百分比排名");
                sb.append(" MAX(CASE subject_relation_id WHEN '"+mapping[0]+"' THEN a.score ELSE '' END ) subjectScore"+(i+1)+", ");
                sb.append(" MAX(CASE subject_relation_id WHEN '"+mapping[0]+"' THEN  a.percentage_g ELSE '' END ) subjectRankG"+(i+1)+", ");
                sb.append(" MAX(CASE subject_relation_id WHEN '"+mapping[0]+"' THEN  a.percentage_s  ELSE '' END ) subjectRankS"+(i+1)+" ");
            }
        }


        sb.append(" FROM dp_subject_score a " +
                " GROUP BY zkz_num " +
                ") t " +
                "LEFT JOIN dp_total_score b on t.zkzNum = b.zkz_num " +
                "LEFT JOIN sparta_exam_student g on g.zkz_num = t.zkzNum "+
                "LEFT JOIN sparta_school d on d.id = g.school_id " +
                "LEFT JOIN excel_student2019 f on f.id_card = b.sfz_num\n"+
                "where  g.exam_uuid = '"+ examuuid +"' and g.e_status = 0 and g.student_id !='000000' ORDER BY b.score+0 desc ");
        String execScoreSql = sb.toString();
        System.out.println("总分sql："+execScoreSql);
        List<ExcelTotal> excelTotals = jdbcTemplate.query(execScoreSql, new BeanPropertyRowMapper<>(ExcelTotal.class));
        writerTotal.setOnlyAlias(true);
        writerTotal.write(excelTotals,true);
        writerTotal.close();
    }

    public void exportXiaoZhaoType(){
//        String examuuid = jdbcTemplate.queryForObject("select uuid from sparta_exam", String.class);
        String sql = " select DISTINCT mapping_subject_id id,name from sparta_subject where relation_subject_id not like '%#%'";
        List<String[]> list = jdbcTemplate.query(sql, (rs, r) -> {
            return new String[]{rs.getString("id"), rs.getString("name")};
        });
        Boolean remove = redisTemplate.hasKey(examUUID + "RemoveSubject");
        if (remove){
            //有,则排除插入
            Object removeSubject = redisTemplate.opsForValue().get(examUUID + "RemoveSubject");
            String removeSubjectStr = removeSubject.toString();
            list = list.stream().filter(s -> !removeSubjectStr.contains(s[0])).collect(Collectors.toList());
        }
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT DISTINCT " +
                "f.name," +
                "t.* from ( SELECT a.sfz_num sfzNum,a.zkz_num zkzNum,");
        int size = list.size();
//            BigExcelWriter writerTotal = ExcelUtil.getBigWriter("D:" + File.separator + examUUID + "Excel/总分.xlsx");
        FileUtils.deleteFile("/data" + File.separator + examUUID + "Excel/总分.xlsx");
        BigExcelWriter writerTotal = ExcelUtil.getBigWriter("/data" + File.separator + examUUID + "Excel/总分.xlsx");
        writerTotal.addHeaderAlias("name","姓名");
        writerTotal.addHeaderAlias("zkzNum","准考证");
        writerTotal.addHeaderAlias("sfzNum","身份证");
        for (int i = 0; i < size ; i++) {
            String[] mapping = list.get(i);
            if(i != size-1) {
                writerTotal.addHeaderAlias("subjectScore"+(i+1),mapping[1]);
                sb.append(" MAX(CASE mapping_subject_id WHEN '"+mapping[0]+"' THEN a.score ELSE '' END ) subjectScore"+(i+1)+", ");
            } else {
                writerTotal.addHeaderAlias("subjectScore"+(i+1),mapping[1]);
                sb.append(" MAX(CASE mapping_subject_id WHEN '"+mapping[0]+"' THEN a.score ELSE '' END ) subjectScore"+(i+1));
            }
        }
        sb.append(" FROM dp_subject_score a " +
                " GROUP BY zkz_num " +
                ") t " +
                "LEFT JOIN dp_total_score b on t.zkzNum = b.zkz_num "+
                "LEFT JOIN sparta_exam_student f ON f.sfz_num = t.sfzNum and f.e_status =0 and f.student_id !='000000'\n"+
                "ORDER BY b.score+0 desc, t.zkzNum+0 desc ");
        String execScoreSql = sb.toString();
        System.out.println("总分sql："+execScoreSql);
        List<ExcelTotal> excelTotals = jdbcTemplate.query(execScoreSql, new BeanPropertyRowMapper<>(ExcelTotal.class));
        writerTotal.setOnlyAlias(true);
        writerTotal.write(excelTotals,true);
        writerTotal.close();
        //改变路线图的download_score状态
        List<RoadMap> status = jdbcTemplate.query("SELECT * from road_map", new BeanPropertyRowMapper<>(RoadMap.class));
        if (CollectionUtil.isNotEmpty(status)){
            RoadMap roadMap = status.get(0);
            Integer tiankong = roadMap.getDownloadScore();
            if (tiankong==0){
                jdbcTemplate.update("update road_map set download_score = 1");
            }
        }
    }

}
